package cn.telling.product.dao.Impl;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.stereotype.Repository;

import cn.telling.common.util.DateTimeTool;
import cn.telling.common.util.UUIDUtil;
import cn.telling.product.dao.IBuyerDao;
import cn.telling.product.vo.BuyerVo;
import cn.telling.utils.AutoInjection;
import cn.telling.utils.BaseUtil;

/**
 * 
 * @ClassName: BuyerDaoImpl 
 * @Description: 买家数据服务接口的实现
 * @author 陈赟 
 * @date 2015年6月17日 下午3:11:43 
 *
 */
@Repository("buyerDao")
public class BuyerDaoImpl implements IBuyerDao {
    
    private static Logger LOGGER = LoggerFactory.getLogger(BuyerDaoImpl.class);
    
    @Autowired
    @Qualifier("film-template")
    private JdbcTemplate jdbcTemplate;
    
	@Autowired
	@Qualifier("oracleLobHandler")
	private LobHandler lobHandler;

    @Override
    public BuyerVo getBuyerByBuyerId(BigDecimal buyerId) {
        StringBuilder sb = new StringBuilder();
        sb.append("WITH BI AS(SELECT BI.USERID,BI.BUYERID,'2' as type,BI.BUYER_TYPE,BI.BUYERSOURCE,BI.SHOPNAME,");
        sb.append("BI.AREAID AS areaid,BI.PROVINCE,BI.CITY FROM BUYERINFO BI ");
        sb.append("WHERE BI.BUYERSTATUS = '02' AND BI.DELFLAG = '1' UNION ALL SELECT CB.USERID,CB.GBUYERID,");
        sb.append("'1' as type,CB.BUYER_TYPE,CB.BUYERSOURCE,CB.SHOPNAME,CB.DISTRICT as areaid,CB.PROVINCE,CB.CITY ");
        sb.append("FROM COMMON_BUYERINFO CB WHERE CB.STATUS = '1') select BI.*, u.username from BI ");
        sb.append("inner join userlogininfo u on bi.USERID = u.userid where BI.BUYER_TYPE is not null and BI.BUYERID = ? ");
        
        String sql = sb.toString();
        Object args [] = new Object[]{buyerId};
        
        LOGGER.debug("根据买家ID查询买家信息: "+ BaseUtil.logSQL(sql,args));
        
        List<BuyerVo> li = jdbcTemplate.query(sql, args, new RowMapper<BuyerVo>(){
            public BuyerVo mapRow(ResultSet rs,int i) throws SQLException{
                BuyerVo vo = new BuyerVo();
                AutoInjection.Rs2Vo(rs, vo, null);
                return vo;
            };
        });
        if (li != null && li.size() > 0)
        {
            return li.get(0);
        }
        return null;
    }

    @Override
    public List<BuyerVo> getBuyerBySupplyId(String supplyId) {
        StringBuilder sb = new StringBuilder();
        sb.append("WITH BI AS(SELECT BI.USERID,BI.BUYERID,'2' as type,BI.BUYER_TYPE,BI.BUYERSOURCE,BI.SHOPNAME,");
        sb.append("BI.AREAID AS areaid,BI.PROVINCE,BI.CITY FROM BUYERINFO BI ");
        sb.append("WHERE BI.BUYERSTATUS = '02' AND BI.DELFLAG = '1' UNION ALL SELECT CB.USERID,CB.GBUYERID,");
        sb.append("'1' as type,CB.BUYER_TYPE,CB.BUYERSOURCE,CB.SHOPNAME,CB.DISTRICT as areaid,CB.PROVINCE,CB.CITY ");
        sb.append("FROM COMMON_BUYERINFO CB WHERE CB.STATUS = '1') select BI.*, u.username from BI ");
        sb.append("inner join userlogininfo u on bi.USERID = u.userid where BI.BUYER_TYPE is not null and u.userid = ? ");
        
        String sql = sb.toString();
        Object args [] = new Object[]{supplyId};
        
        LOGGER.debug("根据供应商ID查询其销售区域内的所有买家: "+ BaseUtil.logSQL(sql,args));
        
        return jdbcTemplate.query(sql, args, new RowMapper<BuyerVo>(){
            public BuyerVo mapRow(ResultSet rs,int i) throws SQLException{
                BuyerVo vo = new BuyerVo();
                AutoInjection.Rs2Vo(rs, vo, null);
                return vo;
            };
        });
    }
    @Override
	public List<BuyerVo> queryBuyerInfoByAreaId(String areaId,Map<String, String> buyerTypeMap) {
		 StringBuilder sb = new StringBuilder();
		 String type="";
        if(buyerTypeMap!=null&&buyerTypeMap.size()>0){//天联苹果供应商
        	type=getKey(buyerTypeMap);
        	sb.append("SELECT BI.USERID,BI.BUYERID,u.username SHOPNAME,");
	        sb.append("BI.AREAID FROM BUYERINFO BI ");
	        sb.append("join users u on bi.userid=u.userid ");
	        sb.append(" WHERE BI.BUYERSTATUS = '02' AND BI.DELFLAG = '1' AND BI.BUYER_TYPE IS NOT NULL AND BI.BUYER_TYPE IN ("+type+")  "
	        		+ "  AND BI.AREAID in ("+areaId+")");
	        sb.append(" UNION ALL");
	        sb.append(" SELECT CB.USERID,CB.GBUYERID as BUYERID,");
	        sb.append("u.username  as shopname ,CB.DISTRICT as areaid ");
	        sb.append("FROM COMMON_BUYERINFO CB  join users u on cb.userid=u.userid "
	        		+ " WHERE CB.STATUS = '1' AND CB.BUYER_TYPE IS NOT NULL AND CB.BUYER_TYPE  IN ("+type+")  "
	        		+ "AND CB.DISTRICT in ("+areaId+")");
        }else{
        	sb.append("SELECT BI.USERID,BI.BUYERID,u.username SHOPNAME,");
	        sb.append("BI.AREAID FROM BUYERINFO BI ");
	        sb.append("join users u on bi.userid=u.userid ");
	        sb.append(" WHERE BI.BUYERSTATUS = '02' AND BI.DELFLAG = '1' AND BI.BUYER_TYPE IS NOT NULL  "
	        		+ "  AND BI.AREAID in ("+areaId+")");
	        sb.append(" UNION ALL");
	        sb.append(" SELECT CB.USERID,CB.GBUYERID as BUYERID,");
	        sb.append("u.username  as shopname ,CB.DISTRICT as areaid ");
	        sb.append("FROM COMMON_BUYERINFO CB  join users u on cb.userid=u.userid "
	        		+ " WHERE CB.STATUS = '1' AND CB.BUYER_TYPE IS NOT NULL   "
	        		+ "AND CB.DISTRICT in ("+areaId+")");
        }
		  
		 Object args [] = new Object[]{};
		 LOGGER.debug("根据区域查询所有的买家信息: "+ BaseUtil.logSQL(sb.toString(),args));
		 return jdbcTemplate.query(sb.toString(), args, new RowMapper<BuyerVo>(){
	            public BuyerVo mapRow(ResultSet rs,int i) throws SQLException{
	                BuyerVo vo = new BuyerVo();
	                AutoInjection.Rs2Vo(rs, vo, null);
	                return vo;
	            };
	        });
	}
    
    public String getKey(Map<String,String> buyerMap){
    	String str="";
    	for (Map.Entry<String, String> entry : buyerMap.entrySet()) {
    		if("".equals(str)){
    			str=entry.getKey();
    		}else{
    			str+=","+entry.getKey();
    		}
         }
    	return str;
    }

	@Override
	public List<String> queryAreaInfoBySupplyId(List<String> areaList) {
		
		
	    String sql="insert into lg_interface (PK_INTERFACE,VISITTIME,xmlcontent,type,datetag,direction) values (?,?,?,?,?,?)";						
	    jdbcTemplate.execute(sql, 
				new AbstractLobCreatingPreparedStatementCallback(this.lobHandler) {
				protected void setValues(PreparedStatement ps,LobCreator lobCreator) throws SQLException {
					
					lobCreator.setClobAsString(ps, 3, new BigDecimal(1).toString());
				    ps.setString(1, UUIDUtil.getUUID());
					ps.setString(2, DateTimeTool.getDateTimeString(new Date()));		
				    ps.setString(4, "99");
				    ps.setString(5, "luoqingm" ); 
				    ps.setString(6, "into");
				   
				}
			});
		
	    List<String>  areaidList=new ArrayList<String>();
 	    for( int i=0;i<areaList.size();i++){	    	
		 StringBuilder sb = new StringBuilder();
		 sb.append("select ai.areaid  from areainfo ai");
		 sb.append(" start with ai.areaid=?");
		 sb.append("  connect by prior ai.areaid=ai.parentid");
		 
		 System.out.println("--"+areaList.get(i).toString());
		 Object args1 []={areaList.get(i)};
	
		  List<String>  list= jdbcTemplate.query(sb.toString(), args1, new RowMapper<String>(){
	            public String mapRow(ResultSet rs,int i) throws SQLException{
	                return rs.getString("areaid");
	            };
	        });
		  areaidList.addAll(list);
 		 }		  
		 return areaidList;
	}

}
